import pymysql
import datetime
import threading
import tushare as ts
import random,time


def check_code(table_name):
    db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_info', charset='utf8')
    cursor = db.cursor()

    parse_table_sql = "SELECT CODE FROM STOCK_HIST"
    cursor.execute(parse_table_sql)
    result = cursor.fetchall()

    codes = []

    for result_pickle in result:
        co = result_pickle[0]
        if co in codes:
            pass
        else:
            codes.append(co)
    db.close()
    return codes


def get_stock_info(table_name):
    db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_info', charset='utf8')
    cursor = db.cursor()

    query_sql_sql = "SELECT CODE,TIMETOMARKET FROM {0}".format(table_name)
    cursor.execute(query_sql_sql)
    result = cursor.fetchall()
    db.close()
    return result

#建立股票历史数据表格
def create_hist_table(table_name):
    db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_info', charset='utf8')
    cursor = db.cursor()
    #建立日期，开盘价，收盘价，最高价，最低价，交易量字段表格
    create_table_sql = """
                        CREATE TABLE IF NOT EXISTS {0} (
                        DATE DATE NOT NULL,
                        OPEN FLOAT,
                        CLOSE FLOAT,
                        HIGH FLOAT,
                        LOW FLOAT,
                        VOLUME FLOAT,
                        CODE CHAR(6)
                        )
                        """.format(table_name)
    cursor.execute(create_table_sql)
    db.commit()
    check_sql = "SHOW TABLES"
    cursor.execute(check_sql)
    db.close()

#插入表格函数
def stock_hist_insert(table_name,data):
    db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_info', charset='utf8')
    cursor = db.cursor()

    date = data['date']
    open = data['open']
    close = data['close']
    high = data['high']
    low = data['low']
    volume = data['volume']
    code = data['code']

    insert_data = (date,open,close,high,low,volume,code)
    insert_table_sql = "INSERT INTO {0} (DATE,OPEN,CLOSE,HIGH,LOW,VOLUME,CODE) VALUES {1}".format(table_name,insert_data)
    cursor.execute(insert_table_sql)
    db.commit()
    db.close()


#获取代码及上市时间，调用存储函数插入函数存储
def get_stock_hist(code,table_name,timetomarket):
    time_to_market = timetomarket[0:4] + '-' + timetomarket[4:6] + '-' + timetomarket[6:8]
    #日期输出格式化 datetime => string
    now_date = datetime.datetime.now().strftime('%Y-%m-%d')
    #日期输出格式化 string => datetime
    start_date = datetime.datetime.strptime(time_to_market,'%Y-%m-%d')
    today_date = datetime.datetime.strptime(now_date,'%Y-%m-%d')
    end_day = start_date + datetime.timedelta(days=1000)
    error_link = []
    while end_day < today_date:
        start_date = end_day + datetime.timedelta(days=1)
        end_day += datetime.timedelta(days=1000)
        start_date_str = datetime.datetime.strftime(start_date,'%Y-%m-%d')
        end_day_str = datetime.datetime.strftime(end_day,'%Y-%m-%d')
        try:
            stock_hist = ts.get_k_data(code, start=start_date_str, end=end_day_str)
            time.sleep(random.uniform(1, 3))
            for i in range(len(stock_hist)):
                stock_hist_insert(table_name,stock_hist.iloc[i])
        except:
            error = [code,start_date_str,end_day_str]
            error_link.append(error)
    else:
        end_day = today_date
        start_date_str = datetime.datetime.strftime(start_date,'%Y-%m-%d')
        end_day_str = datetime.datetime.strftime(end_day,'%Y-%m-%d')
        try:
            stock_hist = ts.get_k_data(code, start=start_date_str, end=end_day_str)
            for i in range(len(stock_hist)):
                stock_hist_insert(table_name,stock_hist.iloc[i])
        except:
            error = [code,start_date_str,end_day_str]
            error_link.append(error)
    print(error_link)
    while len(error_link) != 0:
        for error in error_link:
            code = error[0]
            start_date_str = error[1]
            end_day_str = error[2]
            try:
                stock_hist = ts.get_k_data(code, start=start_date_str, end=end_day_str)
                for i in range(len(stock_hist)):
                    stock_hist_insert(table_name,stock_hist.iloc[i])
                error_link.remove(error)
            except:
                error = [code,start_date_str,end_day_str]
                error_link.append(error)
    print("{0} is finished!".format(code))





if __name__ == '__main__':
    hist_table = 'stock_hist'
    basics_table = 'stock_info'
    create_hist_table(hist_table)
    threads = []
    codes = check_code(hist_table)
    for query_pickle in get_stock_info(basics_table):
        code = query_pickle[0]
        timetomarket = query_pickle[1]
        if code in codes:
            pass
        else:
            t = threading.Thread(target=get_stock_hist,args=(code,hist_table,timetomarket))
            threads.append(t)
    print(len(threads))
    for t in threads:
        t.setDaemon(True)
        t.start()
        t.join()